using Firestarter.DB.Schema.BO;
using FireStarterModeller.BO;
using Firestarter.DB.Schema.Logic;
using Habanero.Util;

namespace Firestarter.DB.Schema.Logic
{
    public class DatabaseScriptCreator
    {
        public string CreateTable(DBTable dbTable, CreateTableSqlBuilder createTableSqlBuilder)
        {
            AddPrimaryKeyToCreateTableSQLBuilder(dbTable, createTableSqlBuilder);
            AddColumnsToCreateTableSQLBuilder(dbTable, createTableSqlBuilder);
            AddAlternateKeysToCreateTableSQLBuilder(dbTable, createTableSqlBuilder);
            AddForeignKeysToCreateTableSqlBuilder(dbTable, createTableSqlBuilder);

            return createTableSqlBuilder.GetSql();
        }

        private void AddColumnsToCreateTableSQLBuilder(DBTable dbTable, CreateTableSqlBuilder createTableSqlBuilderMySql)
        {
            foreach (DBColumn columnInfo in dbTable.Columns)
            {
                //TODO Mark 11 May 2009: Add Precision to the Column Information
                createTableSqlBuilderMySql.AddColumn(columnInfo.ColumnName, columnInfo.DataType, columnInfo.DataLength, null, columnInfo.Nullable.GetValueOrDefault(true), columnInfo.IsAutoIncrementing.GetValueOrDefault(false));
            }
        }

        protected static CreateTableSqlBuilder AddPrimaryKeyToCreateTableSQLBuilder(DBTable dbTable, CreateTableSqlBuilder createTableSqlBuilderMySql)
        {
            if (dbTable.PrimaryKeyDB == null) return createTableSqlBuilderMySql;
            foreach (DBPrimaryKeyColumn primaryKeyColumnInfo in dbTable.PrimaryKeyDB.PrimaryKeyColumns)
            {
                DBColumn dbColumn = primaryKeyColumnInfo.Column;
                createTableSqlBuilderMySql.AddPrimaryKeyColumn(dbColumn.ColumnName);
            }
            return createTableSqlBuilderMySql;
        }

        protected static CreateTableSqlBuilder AddAlternateKeysToCreateTableSQLBuilder(DBTable dbTable, CreateTableSqlBuilder createTableSqlBuilderMySql)
        {
            foreach (DBKey keyInfo in dbTable.Keys)
            {
                string columnNameList = "";
                foreach (DBKeyColumn keyColumnInfo in keyInfo.KeyColumns)
                {
                    columnNameList = StringUtilities.AppendMessage
                        (columnNameList, keyColumnInfo.Column.ColumnName, ", ");
                }
                createTableSqlBuilderMySql.AddUniqueKey(keyInfo.KeyName, columnNameList);
            }
            return createTableSqlBuilderMySql;
        }

        protected static CreateTableSqlBuilder AddForeignKeysToCreateTableSqlBuilder
            (DBTable dbTable, CreateTableSqlBuilder createTableSqlBuilderMySql)
        {
            foreach (DBForeignKey foreignKeyInfo in dbTable.ForeignKeys)
            {
                string parentColumnNameList = "";
                string referencedColumnNameList = "";
                foreach (DBForeignKeyColumn foreignKeyColumnInfo in foreignKeyInfo.ForeignKeyColumns)
                {
                    parentColumnNameList = StringUtilities.AppendMessage
                        (parentColumnNameList, foreignKeyColumnInfo.ParentColumn.ColumnName, ", ");
                    referencedColumnNameList = StringUtilities.AppendMessage
                        (referencedColumnNameList, foreignKeyColumnInfo.RelatedColumn.ColumnName, ", ");
                }
                createTableSqlBuilderMySql.AddForeignKey
                    (foreignKeyInfo.ForeignKeyName, parentColumnNameList, foreignKeyInfo.RelatedTable.TableName,
                     referencedColumnNameList);
            }
            return createTableSqlBuilderMySql;
        }
        
        //
        //CREATE TABLE track(
        //  album CHAR(10),
        //  dsk INTEGER,
        //  posn INTEGER,
        //  song VARCHAR(255),
        //  FOREIGN KEY (album) REFERENCES album(id)
        //);
/*        public string CreateForeignKey(DBForeignKey dbForeignKey)
        {
            //ALTER TABLE sdas ADD FOREIGN KEY (album) REFERENCES album(id);
            string sql = string.Format("ALTER TABLE {0} ", dbForeignKey.ParentTable.TableName);
            sql += string.Format("ADD CONSTRAINT {0} FOREIGN KEY {0} ", dbForeignKey.ForeignKeyName);
            sql += string.Format("({0})", dbForeignKey.ForeignKeyColumns[0].ParentColumn.ColumnName);
            sql += string.Format("REFERENCES {0} ", dbForeignKey.RelatedTable.TableName);
            sql += string.Format("({0})", dbForeignKey.ForeignKeyColumns[0].RelatedColumn.ColumnName);

            //ALTER TABLE `schemainfo_test`.`testtable` 
            // ADD COLUMN `refid` CHAR(38) NOT NULL DEFAULT '' AFTER `TestColumn`,
            // ADD CONSTRAINT `FK_testtable_1` FOREIGN KEY `FK_testtable_1` (`refid`)
            //    REFERENCES `ref` (`idref`)
            //    ON DELETE RESTRICT
            //    ON UPDATE RESTRICT;
            return sql;
        }*/
    }
}